MySQL-常用命令(二)
函数
表结构如下
select * from vendors;
+---------+-----------+---------------------------+-----------+------------+----------+--------------+
| vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country |
+---------+-----------+---------------------------+-----------+------------+----------+--------------+
| 1 | vend_1 | shanghai_baoshan_jinqiulu | shanghai | shanghai | 200000 | CN |
| 2 | vend_2 | hefei_gaoxin_huangshanlu | hefei | anhui | 300000 | CN |
| 3 | vend_3 | hangzhou_binjiang_binjian | hangzhou | zhejiang | 400000 | CN |
| 4 | vend_4 | ningbo_gaoxin_shijilu | ningbo | zhejiang | 500000 | CN |
+---------+-----------+---------------------------+-----------+------------+----------+--------------+
CONCAT:用于连接字段的值或者字符串,以行的形式显示(注意和GROUP_CONCAT的区别)
SELECT CONCAT(vend_name,'!',vend_country) FROM Vendors;
//输出
+------------------------------------+
| concat(vend_city,'!',vend_country) |
+------------------------------------+
| shanghai!CN |
| hefei!CN |
| hangzhou!CN |
| ningbo!CN |
+------------------------------------+
GROUP_CONCAT:用于连接字段的值或者字符串,以“,”分割显示在一行(注意和CONCAT的区别)
SELECT GROUP_CONCAT(vend_city,vend_country) FROM vendors;
//输出
+----------------------------------------+
| group_concat(vend_city,vend_country) |
+----------------------------------------+
| shanghaiCN,hefeiCN,hangzhouCN,ningboCN |
+----------------------------------------+
GROUP_BY: 对行分组,但输出的值未必是原数据的同一行。选择没有被GROUP_BY的字段值具有不确定性,特别是和MAX(),MIN()函数结合使用时。
select vend_state, count(*) as state_num from vendors group by vend_state;
//输出
+------------+-----------+
| vend_state | state_num |
+------------+-----------+
| anhui | 1 |
| shanghai | 1 |
| zhejiang | 2 |
+------------+-----------+
#条件分组 HAVING条件
select vend_state, count(*) as state_num from vendors group by vend_state having state_num > 1;
//输出
+------------+-----------+
| vend_state | state_num |
+------------+-----------+
| zhejiang | 2 |
+------------+-----------+
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。